﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace TSCC.DataAccess
{
    public class DA_Report:DA_Matrix
    {
        protected string fieldItem = string.Empty;
        /// <summary>
        /// 給總表一用，因為總表一二的計算方式不同
        /// </summary>
        protected string fieldItem1 = string.Empty;
        protected List<string> criteriaList = null;

        public DA_Report()
        {
            tableSchema = new Dictionary<string, DbType>();

            tableSchema.Add("日期", DbType.Date);
            tableSchema.Add("例外項目", DbType.String);
            tableSchema.Add("組別", DbType.String);

            criteriaList = new List<string>();
        }

        public DataTable GetSummaryData1(List<string> p_dateList
                            , List<string> p_itemList
                            , List<string> p_team
                            )
        {
            
            string sql = string.Format(getSummaryStatement1()
                                , fieldItem1
                                , criteriaList[0].ToString()
                                , criteriaList[1].ToString()
                                );
            createCommand(sql);

            for (int i = 0; i < 4; i++)
            {
                foreach (string str in p_dateList)
                {
                    cmd.Parameters.Add(getParameter(str, DbType.Date));
                }


                foreach (string str in p_team)
                {
                    cmd.Parameters.Add(getParameter(str, DbType.String));
                }

                foreach (string str in p_itemList)
                {
                    cmd.Parameters.Add(getParameter(str, DbType.String));
                }
            }

            DataTable dt = TSCC.Common.DAL.GetDataTable(cmd);

            if (dt.Rows.Count > 0)
            {
                string[] colNameArray = { "提報時間", "比例" };
                int x = 2;
                while (x < dt.Columns.Count)
                {
                    for (int y = 0; y < colNameArray.Length; y++)
                    {
                        dt.Columns[x + y].Caption = colNameArray[y];
                    }
                    x = x + colNameArray.Length;
                }            
            }

            return dt;
        }

        public DataTable GetSummaryData2(List<string> p_dateList
                            , List<string> p_itemList
                            , List<string> p_team)
        {
            string sql = string.Format(getSummaryStatement2()
                               , fieldItem + fieldItem1
                               , criteriaList[0].ToString()
                               , criteriaList[1].ToString()
                               );
            createCommand(sql);

            for (int i = 0; i < 3; i++)
            {
                foreach (string str in p_dateList)
                {
                    cmd.Parameters.Add(getParameter(str, DbType.Date));
                }

                foreach (string str in p_team)
                {
                    cmd.Parameters.Add(getParameter(str, DbType.String));
                }

                foreach (string str in p_itemList)
                {
                    cmd.Parameters.Add(getParameter(str, DbType.String));
                }
            }

            DataTable dt = TSCC.Common.DAL.GetDataTable(cmd);
            //if (dt.Rows.Count > 0)
            //{
            //    string[] colNameArray = { "回電時間", "結案筆數","平均結案件數/時" };
            //    int x = 2;
            //    while (x < dt.Columns.Count)
            //    {
            //        for (int y = 0; y < colNameArray.Length; y++)
            //        {
            //            dt.Columns[x + y].Caption = colNameArray[y];
            //        }
            //        x = x + colNameArray.Length;
            //    }
            //}

            return dt;
        }

        public DataTable GetDetailData1(List<string> p_dateList                            
                            , List<string> p_team)
        {
            string sql = string.Format( getDetailStatement1()
                             , criteriaList[0].ToString()
                             );
            createCommand(sql);
            cmd.Parameters.Add(getParameter(p_dateList[0].ToString()
                                    , DbType.Date,"D1"));
            cmd.Parameters.Add(getParameter(p_dateList[1].ToString()
                                     , DbType.Date, "D2"));

            foreach (string str in p_team)
            {
                cmd.Parameters.Add(getParameter(str, DbType.String));
            }

           
            DataTable dt = TSCC.Common.DAL.GetDataTable(cmd);
            return dt;
        }

        public DataTable GetDetailData2(List<string> p_dateList
                           , List<string> p_team)
        {
            string sql = string.Format(getDetailStatement2()
                             , criteriaList[0].ToString()
                             );
            createCommand(sql);
            cmd.Parameters.Add(getParameter(p_dateList[0].ToString()
                                    , DbType.Date, "D1"));
            cmd.Parameters.Add(getParameter(p_dateList[1].ToString()
                                     , DbType.Date, "D2"));

            foreach (string str in p_team)
            {
                cmd.Parameters.Add(getParameter(str, DbType.String));
            }


            DataTable dt = TSCC.Common.DAL.GetDataTable(cmd);
            return dt;
        }

    
        /// <summary>
        /// 有可能使用者沒有選，就是全選，此時不用當成where 條件
        /// </summary>
        /// <param name="p_itemList"></param>
        /// <param name="p_fillField">true 表示要item放入到where 中</param>
        public void AddItemCriteria(List<string> p_itemList,bool p_fillField)
        { 
           
            fieldItem = string.Empty;
            StringBuilder fieldBuilder = new StringBuilder();
            StringBuilder fieldBuilder1 = new StringBuilder();
            string str1 = string.Empty;
            foreach (string str in p_itemList)
            {
                str1 = str;
                if (str.Substring(0, 2).Equals("其他"))
                {
                    str1 = str.Substring(0, 2);
                }

                fieldBuilder.AppendFormat(@"
                           , MAX(IIF(A.ITEM = '{0}',A.OUT_TIME,0))AS [{1}_1]
                           , MAX(IIF(A.ITEM = '{0}',A.OUT_COUNT,0)) AS [{1}_2]
                           , MAX(IIF(A.ITEM = '{0}' AND A.OUT_TIME > 0 AND A.OUT_COUNT > 0
			               , ROUND(A.OUT_TIME/A.OUT_COUNT,0),0)) AS [{1}_3]", str, str1);

                fieldBuilder1.AppendFormat(@",MAX(IIF(A.ITEM = '{0}',A.OUT_TIME,0)) AS [{1}_1]
                                            ,MAX(IIF(A.ITEM = '{0}' AND A.TOT_TIME > 0 AND A.OUT_TIME > 0
			                                    , CSTR(ROUND(A.OUT_TIME/A.TOT_TIME,2)*100) + '%','0%')) AS [{1}_2]"
                                        , str,str1);
            }
            fieldItem = fieldBuilder.ToString();
            fieldItem1 = fieldBuilder1.ToString();

            string itemCriteria;
            itemCriteria = " AND 1=1 ";

            if (p_fillField == true)
            {
                if (p_itemList.Count > 0)
                {
                    var parms = p_itemList.Select((s) => string.Format("?{0}", "")).ToArray();
                    var inclause = string.Join(",", parms);
                    itemCriteria = string.Format(" AND 例外項目 in({0})", inclause);
                }
            }

            criteriaList.Add(itemCriteria);
        }

        public void AddItemCriteria(List<string> p_itemA, List<string> p_itemB, bool p_fillField)
        {
            StringBuilder sbItemA = new StringBuilder();
            StringBuilder sbItemB = new StringBuilder();
            string itemCriteria,itemCriteriaA, itemCriteriaB, str1;
            fieldItem = string.Empty;
            fieldItem1 = string.Empty;
            foreach (string str in p_itemA)
            {
                str1 = str;
                sbItemA.AppendFormat(@"
                           , MAX(IIF(A.ITEM = '{0}',A.OUT_TIME,0))AS [{1}_1]
                           , MAX(IIF(A.ITEM = '{0}',A.OUT_COUNT,0)) AS [{1}_2]
                           , MAX(IIF(A.ITEM = '{0}' AND A.OUT_TIME > 0 AND A.OUT_COUNT > 0
			               , ROUND(A.OUT_TIME/A.OUT_COUNT,0),0)) AS [{1}_3]", str, str1);
            }

            foreach (string str in p_itemB)
            {
                str1 = str;
                if (str.Substring(0, 2).Equals("其他"))
                {
                    str1 = str.Substring(0, 2);
                }
                 sbItemB.AppendFormat(@"
                         , MAX(IIF(A.ITEM = '{0}',A.OUT_TIME,0))AS [{1}_1]
                         , MAX(IIF(A.ITEM = '{0}',A.CNT,0)) AS [{1}_2]
                         , MAX(IIF(A.ITEM = '{0}' AND A.OUT_TIME > 0 AND A.CNT > 0
                         , ROUND(A.OUT_TIME/A.CNT,0),0)) AS [{1}_3]"


                                       , str, str1);
            }
            fieldItem = sbItemA.ToString();
            fieldItem1 = sbItemB.ToString();
            itemCriteria = string.Empty;
            itemCriteriaA = " AND 1=1 ";
            itemCriteriaB = " AND 1=1 ";
            if (p_fillField == true)
            {
                if (p_itemA.Count > 0)
                {
                    var parms = p_itemA.Select((s) => string.Format("?{0}", "")).ToArray();
                    itemCriteriaA = string.Join(",", parms);
                }
                if (p_itemB.Count > 0)
                {
                    var parms = p_itemA.Select((s) => string.Format("?{0}", "")).ToArray();
                    itemCriteriaB = string.Join(",", parms);
                }
                if (itemCriteriaA.Length > 0 & itemCriteriaB.Length > 0)
                {
                    itemCriteria = string.Format(" AND 例外項目 in({0},{1})"
                        , itemCriteriaA, itemCriteriaB);
                }
                else
                {
                    if (itemCriteriaA.Length > 0)
                    {
                        itemCriteriaB = string.Format(" AND 例外項目 in({0})", itemCriteriaA);
                    }
                    if (itemCriteriaB.Length > 0)
                    {
                        itemCriteriaB = string.Format(" AND 例外項目 in({0})", itemCriteriaB);
                    }
                }
            }
            criteriaList.Add(itemCriteria);
        }

        public void AddTeamCriteria(List<string> p_team)
        {
            string team = " AND 1=1 ";
            if (p_team.Count > 0)
            {
                var parms = p_team.Select((s) => string.Format("?{0}", "")).ToArray();
                var inclause = string.Join(",", parms);
                team = string.Format(" AND 組別 in({0})", inclause);
            }
            criteriaList.Add(team);
        }

        protected string getSummaryStatement1()
        {
            //0 → 是指當ROW TO COLUMN 時要用的EX:SUM(IIF(A.ITEM = '專案執行',A.OUT_TIME,0)) AS 專案執行_1
            //1 → 是WHERE 條件式之日期
            //==================================
            return @"SELECT
	MAX(IIF(A.TEAM2 = '',A.TEAM,A.TEAM2)) AS TEAM
	,MAX(A.TOT_TIME) AS TOT_TIME
	{0}	
FROM
(
SELECT
	A.TEAM
	,'' AS TEAM2
	,B.OUT_TIME AS TOT_TIME
	,A.ITEM
	,A.OUT_TIME
FROM(
	SELECT 
		IIF(MID(A.組別,1,1) = '銀'
				,MID(A.組別,1,3)
				,MID(A.組別,1,2)) AS TEAM
		, A.例外項目 AS ITEM
		, SUM(A.out_time) AS OUT_TIME
	FROM ExceptionLog AS A
	WHERE ( A.日期 Between ? And ?  )
        {1}
        {2}
	GROUP BY IIF(MID(A.組別,1,1) = '銀'
				,MID(A.組別,1,3)
				,MID(A.組別,1,2)),A.例外項目 
)A LEFT JOIN(									
			SELECT 
				IIF(MID(A.組別,1,1) = '銀'
						,MID(A.組別,1,3)
						,MID(A.組別,1,2)) AS TEAM
				, SUM(A.out_time) AS OUT_TIME
			FROM ExceptionLog AS A
			WHERE ( A.日期 Between ? And ?  )
                {1}
                {2}
			GROUP BY IIF(MID(A.組別,1,1) = '銀'
						,MID(A.組別,1,3)
						,MID(A.組別,1,2))
			)B
ON A.TEAM = B.TEAM		
UNION ALL
SELECT
	IIF(MID(A.TEAM,1,1) = '銀'
				,MID(A.TEAM,1,3)
				,MID(A.TEAM,1,2)) AS TEAM
	,B.TEAM AS TEAM2
	,A.OUT_TIME AS TOT_TIME
	,B.ITEM
	,B.OUT_TIME
FROM (
SELECT 
	A.組別 AS TEAM
	, SUM(A.out_time) AS OUT_TIME
FROM ExceptionLog AS A
WHERE ( A.日期 Between ? And ? )
    {1}
    {2}
GROUP BY A.組別
)A LEFT OUTER JOIN (
					SELECT 
											A.組別 AS TEAM
											, A.例外項目 AS ITEM
											, SUM(A.out_time) AS OUT_TIME
										FROM ExceptionLog AS A
										WHERE ( A.日期 Between ? And ?  )
                                            {1}
                                            {2}
										GROUP BY A.組別,A.例外項目 
					)B
ON A.TEAM = B.TEAM	
)AS A 
GROUP BY A.TEAM,A.TEAM2
";
//            return @"
//SELECT
//	A.TEAM	
//	{0}
//FROM(
//	SELECT
//		A.TEAM
//		,B.ITEM
//		,IIF( ISNULL(B.OUT_TIME) ,0,B.OUT_TIME) AS OUT_TIME
//		,IIF( ISNULL(B.OUT_COUNT) ,0,B.OUT_COUNT) AS OUT_COUNT	
//	FROM(
//			SELECT DISTINCT A.組別 AS TEAM
//			FROM CSREmployee AS A
//            WHERE 1=1
//            {2}
//		)A LEFT JOIN 
//		(
//			SELECT 
//					A.組別 AS TEAM
//					, A.例外項目 AS ITEM
//					, SUM(A.out_time) AS OUT_TIME
//					, SUM(A.實際產能) AS OUT_COUNT
//				FROM ExceptionLog AS A
//				WHERE ( A.日期 Between ? And ? )
//				{1}
//				GROUP BY A.組別,A.例外項目 
//		)B
//	ON A.TEAM = B.TEAM
//	)A 
//GROUP BY A.TEAM
//
//";
        }

        protected string getSummaryStatement2()
        {
            return @"SELECT
	A.TEAM AS CSR
	,A.TEAM2 AS TEAM
	{0}

FROM(
	SELECT
		'' as TEAM
		,A.TEAM2	
		,B.ITEM
		,SUM(B.OUT_TIME) AS  OUT_TIME
		,SUM(B.OUT_COUNT) AS OUT_COUNT
        ,SUM(B.CNT) AS CNT
	FROM
	(
		SELECT
			A.TEAM
			,IIF(MID(A.TEAM,1,1) = '銀'
				,MID(A.TEAM,1,3)
				,MID(A.TEAM,1,2)) AS TEAM2
		FROM
		(
			SELECT DISTINCT 
				A.組別 AS TEAM
			FROM CSREmployee AS A
			WHERE 組別 <> '客服管理組'
		)A
	)A INNER JOIN (
						SELECT 
							A.組別 AS TEAM
							, A.例外項目 AS ITEM
							, SUM(A.out_time) AS OUT_TIME
							, SUM(A.實際產能) AS OUT_COUNT
                            , COUNT(*) AS CNT
						FROM ExceptionLog AS A
						WHERE ( A.日期 Between ? And ? )
						 {1}
						 {2}
						GROUP BY A.組別,A.例外項目
						)B
	ON A.TEAM = B.TEAM
	GROUP BY A.TEAM2,B.ITEM	

	UNION ALL

	SELECT
			'' AS TEAM
			,IIF(B.TEAM IS NULL,A.TEAM,B.TEAM) AS TEAM2
			,B.ITEM
			,IIF(B.OUT_TIME IS NULL,0,B.OUT_TIME) AS OUT_TIME
			,IIF(B.OUT_COUNT IS NULL,0,B.OUT_COUNT) AS OUT_COUNT
            ,IIF(ISNULL(B.CNT),0,B.CNT) AS CNT
	FROM
	(SELECT DISTINCT 
				A.組別 AS TEAM
	FROM CSREmployee AS A
	WHERE 組別 <> '客服管理組'
	)A INNER JOIN (
					SELECT 
							A.組別 AS TEAM
							, A.例外項目 AS ITEM
							, SUM(A.out_time) AS OUT_TIME
							, SUM(A.實際產能) AS OUT_COUNT
                            , COUNT(*) AS CNT
						FROM ExceptionLog AS A
						WHERE ( A.日期 Between ? And ? )
						 {1}
						 {2}
						GROUP BY A.組別,A.例外項目
					)B
	ON A.TEAM = B.TEAM

	UNION ALL

	SELECT
			A.CSR
			,A.TEAM
			,B.ITEM
			,IIF(ISNULL(B.OUT_TIME),0,B.OUT_TIME) AS OUT_TIME
			,IIF(ISNULL(B.OUT_COUNT),0,B.OUT_COUNT) AS OUT_COUNT
            ,IIF(ISNULL(B.CNT),0,B.CNT) AS CNT
		FROM
		(
			SELECT DISTINCT 
				A.員編 + '_' + A.姓名 AS CSR
                ,A.員編 AS CSRNO
				,A.組別 AS TEAM
			FROM CSREmployee AS A
		)A LEFT JOIN

		(
			SELECT 
				A.員編 AS CSRNO
				, A.例外項目 AS ITEM
				, SUM(A.out_time) AS OUT_TIME
				, SUM(A.實際產能) AS OUT_COUNT
                , COUNT(*) AS CNT
			FROM ExceptionLog AS A
			WHERE ( A.日期 Between ? And ? )
				{1}
                {2}
			GROUP BY A.員編,A.例外項目
		)B ON A.CSRNO = B.CSRNO
)A 
GROUP BY A.TEAM,A.TEAM2
ORDER BY A.TEAM2,A.TEAM


";
        }

        protected string getDetailStatement1()
        {
            return @"SELECT 
	                    A.日期
	                    , A.員編
	                    , A.組別
	                    , A.姓名
	                    , A.例外項目
	                    , A.例外時間起
	                    , A.例外時間迄
	                    , A.out_time
	                    , A.實際產能
	                    , A.聯絡未果
	                    ,IIF(A.OUT_TIME = 0
		                    ,0
		                    ,ROUND(A.實際產能 / A.OUT_TIME/60,1)
		                    ) AS [平均結案件數]
	                    , A.備註	
                    FROM ExceptionLog AS A
                    WHERE ( A.日期 Between @D1 And @D2 )
                     AND A.例外項目 IN ('警示帳戶','CALL OUT')
                    {0}
                    ORDER BY A.組別 ";
        }

        protected string getDetailStatement2()
        {
            return @"SELECT 
	                    a.日期
	                    , a.員編
	                    , a.組別
	                    , a.姓名
	                    , a.例外項目
	                    , a.例外時間起
	                    , a.例外時間迄
	                    , a.out_time
	                    , a.備註
                    FROM ExceptionLog AS a
                    WHERE ( A.日期 Between @D1 And @D2 )                    
                    {0}
";
        }
    }
}
